if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetWIPProtocolProcesses]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetWIPProtocolProcesses]
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO      
/******************************************************************  
* Name:   GetWIPProtocolProcesses  
* Purpose:  Retrieves the details(from multiple tables using joins) according to the filter parameters  
*  
* PARAMETERS  
* Name        Description       
* -------------    -------------------------------------------  
* @delimittedString      ntext of delimitted string    
* @domainUserId   FrameworkDomainUserId of the looged in(or Impersonated) user-Required    
  
******************************************************************/  
CREATE PROCEDURE [dbo].[GetWIPProtocolProcesses]  
(  
@delimittedString varchar(max),  
@domainuserid  int                    
)  
  
AS BEGIN  
  
SET NOCOUNT ON  
  
 DECLARE @protocolresourcetypeid varchar(2)  
 set @protocolresourcetypeid = (select [id] from FwkResourceType where [Name] = 'Protocol')  
  
 Declare @protocolProcessIds table (Sequence int identity, [ID] int,[ProtocolProcessId] int)                        
 insert into @protocolProcessIds select [Id],[Value] from [GetValueTableOfDelimittedString] (@delimittedString,',')                        
  --Changed the order of the coulumns in the resulset to match the one in the UI.
 SELECT  
  pp.[Id] as ProtocolProcessId,  
  p.ProtocolNumber,
  (CASE pp.[Name] WHEN '' THEN rfc.RefMeaning ELSE rfc.RefMeaning+' ('+pp.[Name]+')' END) as RefMeaning,     
  (adp.LastName +', '+adp.FirstName) as PIName,    
  RTRIM(LTRIM(p.Title)) AS Title, 
  pp.CreateDate,  
  pp.LastModifiedDate,  
  (admp.LastName +', '+admp.FirstName )as LastModifiedBy,  
  p.[Id] as ProtocolId,   
  arl.AccessLevel,
  case when len(p.Title) > 30 then substring(p.Title,1,27)+'...' else p.Title end as ShortTitle,
  pp.Type,
 (ads.[Name]) as Sponsor

 FROM @protocolProcessIds protocolprocessTemp  
 INNER JOIN ProtocolProcess pp   
  on protocolprocessTemp.ProtocolProcessId = pp.Id  
  
 INNER JOIN dbo.protocol p   
  ON pp.ProtocolId=p.Id  
 INNER JOIN dbo.fnAdmQGetAuthorizedResourceList(@domainUserId, @protocolresourcetypeid) arl  
  on arl.resourceid = p.fwkresourceid   
 LEFT JOIN dbo.RefCode rfc  
  ON rfc.[RefCode]=pp.Type  
 LEFT JOIN dbo.ADMPerson adp  
  ON adp.[Id]=p.PIPersonId  
   
 LEFT JOIN dbo.ADMPerson admp  
  ON admp.[FwkDomainUserId]=pp.LastModifiedByUserId  
  
 LEFT JOIN dbo.ADMSponsor ads  
  ON ads.[Id]=p.ADMSponsorId  
 LEFT JOIN dbo.FWKDomainOrganization FDO  
  ON FDO.[Id]=p.PIFwkDomainOrganizationId  
  
 --As the Passed in Ids are sorted when fetched 
 --it will be enough to order by the Id column of the temporary table.
 Order by protocolprocessTemp.Id Asc
--p.ProtocolNumber DESC, p.Title ASC  
END  
  